import pymysql
import datetime
import tushare as ts
import time
import random


def check_hist_data():
    #基础信息数据库链接
    basic_db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_info', charset='utf8')
    basic_cursor = basic_db.cursor()
    #股票行情数据库链接
    hist_db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_hist', charset='utf8')
    hist_cursor = hist_db.cursor()

    #从基础信息数据库导出股票代码及上市信息
    stock_info_sql = "SELECT CODE,TIMETOMARKET FROM STOCK_BASICS"

    basic_cursor.execute(stock_info_sql)
    stock_results = basic_cursor.fetchall()

    error_codes = []

    end_date = datetime.datetime.now().strftime('%Y-%m-%d')

    for stock_result in stock_results:
        code = stock_result[0]
        timetomarket = stock_result[1]

        check_sql = "SELECT COUNT(*) FROM `{0}`".format(code)
        hist_cursor.execute(check_sql)
        record = [code, timetomarket]
        check_result = hist_cursor.fetchone()

        if check_result[0] == 0:
            print(record)
            error_codes.append(record)
        else:
            pass

    for error_code in error_codes:
        code = error_code[0]
        timetomarket = error_code[1]
        time_to_market = timetomarket[0:4] + '-' + timetomarket[4:6] + '-' + timetomarket[6:8]
        time.sleep(random.uniform(1, 3.5))

"""
        try:
            stock_hists = ts.get_k_data(code,start=time_to_market,end=end_date)
            sql_head = "INSERT INTO `{0}` (DATE,OPEN,CLOSE,HIGH,LOW,VOLUME,CODE) VALUES ".format(code)
            for i in range(len(stock_hists)):
                stock_hist = stock_hists.iloc[i]
                date = stock_hist['date']
                open = stock_hist['open']
                close = stock_hist['close']
                high = stock_hist['high']
                low = stock_hist['low']
                volume = stock_hist['volume']
                code = stock_hist['code']
                insert_data = (date,open,close,high,low,volume,code)
                sql_head += str(insert_data) + ","
            sql = sql_head.rstrip(',')
            hist_cursor.execute(sql)
            hist_db.commit()
        except:
            sql = "INSERT INTO STOCK_ERROR (CODE,START_DATE,END_DATE) VALUES ('{0}','{1}','{2}')".format(code,time_to_market,end_date)
            basic_cursor.execute(sql)
            basic_db.commit()
            print("ERROR,{0}".format(code))
        print("{0} is finished".format(code))
    hist_db.close()
    basic_db.close()
"""
if __name__ == '__main__':
    check_hist_data()




